-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		duylv00360
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE InsertProductLoaf 
	-- Add the parameters for the stored procedure here
	@IsImport bit = 1,
	@OrderID int = 0, 
	@ProductID int = 0,
	@WarehouseID int = 0,
	@Quantity numeric(10,0) = 0,
	@PricePerUnit numeric(10,0) = 0,
	@Result int = 0 OUTPUT
	 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    -- Begin transaction 
    BEGIN TRANSACTION 
		INSERT INTO ProductLoaf(
					OrderID,
					ProductID,
					WarehouseID,
					Quantity,
					PricePerUnit
					)
		VALUES		(
					@OrderID,
					@ProductID,
					@WarehouseID,
					@Quantity,
					@PricePerUnit
		)
	SAVE TRANSACTION InsertProductLoaf
	IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertProductLoaf
	-- check @isImport
	IF @IsImport = 1
		BEGIN
			IF EXISTS (SELECT WarehouseProduct.ProductID FROM WarehouseProduct WHERE WarehouseProduct.ProductID = @ProductID AND WarehouseProduct.WarehouseID = @warehouseID)
				BEGIN
					UPDATE	WarehouseProduct
					SET		AveragePricePerUnit = (AveragePricePerUnit * Quantity + @Quantity * @PricePerUnit)/(Quantity + @Quantity),Quantity = Quantity+@quantity
					WHERE	WarehouseProduct.ProductID = @ProductID
							AND 
							WarehouseProduct.WarehouseID = @warehouseID		
					-- set @result = 1
					SET @result = 1;
				END
			ELSE
				BEGIN
					INSERT INTO WarehouseProduct(ProductID, WarehouseID, Quantity, AveragePricePerUnit)
					VALUES ( @ProductID, @warehouseID, @quantity, @PricePerUnit);	
					-- set @result = 1
					SET @result = 1;			
					
				END 
			SAVE TRANSACTION InsertProductLoaf
			IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertProductLoaf
		END
	ELSE
		BEGIN
			DECLARE @totalQuantity int = 0;
			SET @totalQuantity = (SELECT WarehouseProduct.Quantity FROM WarehouseProduct WHERE WarehouseProduct.ProductID = @ProductID AND WarehouseProduct.WarehouseID = @warehouseID);
			IF (@quantity <= @totalQuantity)
				BEGIN					
					UPDATE	WarehouseProduct
					SET		Quantity = Quantity - @quantity
					WHERE	WarehouseProduct.ProductID = @ProductID
							AND 
							WarehouseProduct.WarehouseID = @warehouseID;	
					-- set @result = 0
					SET @result = 0;				 
				END
			ELSE
				BEGIN
					PRINT 'Not enough product in this warehouse!';		
					-- set @result = -1
					SET @result = -1;			
				END
			
			SAVE TRANSACTION InsertMaterialLoaf
			IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertMaterialLoaf
		END		
	COMMIT TRANSACTION
END
GO

/*
use WarehouseManagement
go
exec InsertProductLoaf @orderId = 44,@productID=  2,@warehouseID= 1, @quantity= 10, @isimport = 0
*/
